﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.IO;

public partial class ToExcel : System.Web.UI.Page
{
    ExcelHelper ehelper;
    MsSqlHelper shelper;
    
    CSClass CS = new CSClass();
    DataTable dt;
    protected void Page_Load(object sender, EventArgs e)
    {
        string id=Request.Params["id"];
        string month = Request.Params["month"];
        if (!IsPostBack)
        {
            if (!string.IsNullOrEmpty(id))
            {
                Data_Binding(id,month);
            }
        }

        Literal1.Text = list();

    }

    public string list()
    {
        string result = "";
        shelper = new MsSqlHelper();
        string sql = "select ROW_NUMBER() over(order by rqtime) as xsort, rtrim(djbh) as djbh,rtrim(rqtime) as rqtime,je from [Web_Laborage] order by rqtime desc";
        dt = shelper.ExecuteDt(sql);

        result += "<table border=\"1\" style=\"border:1px solid #ccc;width:100%;\">\r\n"
                + "<tbody>\r\n"
                + "<tr height=\"50\" style=\"background: #eee;text-align: center\">\r\n"
                + "<td width=\"10%\">序号</td><td width=\"15%\">单据编号</td><td width=\"25%\">月份</td><td width=\"25%\">金额</td><td width=\"25%\">操作</td>\r\n"
                + "</tr>";
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            result += "<tr height=\"50\" style=\"text-align: center\">\r\n"
                    + "<td>" + dt.Rows[i]["xsort"] + "</td>\r\n"
                    + "<td class=\"drug_name\">" + dt.Rows[i]["djbh"] + "</td>\r\n"
                    + "<td class=\"drug_point\">" + dt.Rows[i]["rqtime"] + "</td>\r\n"
                    + "<td class=\"drug_rule\">" + dt.Rows[i]["je"] + "</td>\r\n"
                    + "<td><a href=\"?month=" + dt.Rows[i]["rqtime"] + "&id=" + dt.Rows[i]["djbh"] + "\">下载</a></td>\r\n"
                    + "</tr>\r\n";
        }
        result += "</table>\r\n";
        return result;
    }

    private void Data_Binding(string djbh,string month)
    {
        try
        {
            shelper = new MsSqlHelper();
            shelper.Conn.Open();
            MsSqlDao msdao = new MsSqlDao();
            object[,] title = { { "序号", "员工编号", "账号", "姓名", "部门", "金额", "证件号码", "证件类型", "地址", "电话", "开户银行", "税前工资", "税额" } };
            string sql;
            sql = "select replace(a.ygbh,'','') as ygbh,replace(rtrim(a.yhzhh),'','') as yhzhh,replace(rtrim(a.xingming),'','') as xingming,rtrim(c.DepartmentName) as DepartmentName,b.shje as je,"
                + "rtrim(a.idcard) as idcard,0 zjlx,replace(zyAddress,'','') zyAddress,replace(telephoneno,'','') telephoneno,rtrim(a.khyh) as khyh,b.sfgz,b.taxje"
                + " from [Web_UserInfo] a,[Web_LaborageDetailed] b,[Web_Department] c"
                + " Where a.beactive='是' and a.is_gz in('否','新') and a.ygbh=b.ygbh and a.bmid=c.Did "
                + "and b.djbh='" + djbh + "' and str(b.sfgz)<>'' and a.state<>'离职' and b.sfgz<>0 order by c.bmid,a.khyh";

            dt = msdao.FindAll(shelper.Conn, sql);
            NPOIExportExclHelper excelHelper = new NPOIExportExclHelper();
            MemoryStream ms = excelHelper.RenderDataTableToExcel(dt, title, "工资数据", title.Length) as MemoryStream;

            Response.Clear();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";
            //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            string fileName = HttpUtility.UrlEncode(string.Format("{1}_{0:yyyy-MM-dd_HH_mm}.xls", System.DateTime.Now, month.ToString() + "工资表"));

            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName));
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
            Response.Charset = "utf-8";
            Response.BinaryWrite(ms.ToArray());
            ms.Close();
            ms.Dispose();
            CS.WriteLog(sql.ToString(), true);
        }
        catch (Exception ex)
        {
            CS.WriteLog(ex.ToString(), true);
        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        Button btn = (Button)sender;//注意控件类型的转换
        string id=btn.CommandArgument;
        //Data_Binding(id);
    }
}
    
